package com.andnnl.table;

import java.util.ArrayList;
import java.util.List;

/**
 * Created by chenss on 2022/11/28.
 */
public class GenSqlAddColumn {

    public static void main(String[] args) {

        for (int i = 1; i <= 16 ; i++) {
            System.out.println(fm(i));
        }
//        genCacheTableAddColumn();
//        genHistoryTableAddColumn();
//        TBL_SMS_MOBILES_CACHE
//        'ALTER TABLE dbo.TBL_SMS_MOBILES_',@cur_str,'   ADD city varchar(50) NULL;',
//        'ALTER TABLE dbo.TBL_SMS_MOBILES_',@cur_str,'   ADD CONSTRAINT DF_TBL_SMS_MOBILES_',@cur_str,'_city DEFAULT '''' FOR city;',
    }


    private static void genHistoryTableAddColumn() {

        //TBL_SMS_LONG_MSGID_202008_61
        String sqlHead =" USE [EMAS_SMS_SYNC] \n"
                + " GO \n";

        String sql =" /****** 202210 ******/ \n" +
                " ALTER TABLE [EMAS_SMS_MC_202210].[dbo].[TBL_SMS_MOBILES_202210] ADD idMg bigint NULL; \n"
                + " ALTER TABLE [EMAS_SMS_MC_202210].[dbo].[TBL_SMS_MOBILES_202210] ADD cidMg bigint NULL; \n"
                + " ALTER TABLE [EMAS_SMS_MC_202210].[dbo].[TBL_SMS_CONTENT_202210] ADD idMg bigint NULL; \n";

        String sqlIdx = " CREATE NONCLUSTERED INDEX [idx_cidmg_cid] ON [EMAS_SMS_MC_202210].[dbo].[TBL_SMS_MOBILES_202210] \n"
                + " ( \n"
                + " 	[cidMg] ASC, \n"
                + " 	[CID] ASC \n"
                + " )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] \n"
                + " GO \n"

                + " CREATE NONCLUSTERED INDEX [idx_idmg] ON [EMAS_SMS_MC_202210].[dbo].[TBL_SMS_MOBILES_202210] \n"
                + " ( \n"
                + " 	[idMg] ASC \n"
                + " )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] \n"
                + " GO \n"
                + " CREATE NONCLUSTERED INDEX [idx_idmg] ON [EMAS_SMS_MC_202210].[dbo].[TBL_SMS_CONTENT_202210] \n"
                + " ( \n"
                + " 	[idMg] ASC \n"
                + " )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] \n"
                + " GO \n";

        int beforeYear=201905;
        int endYear=202211;
        List<Integer> rs = getYearMonthList(beforeYear, endYear);

        for (Integer yyyyMM : rs) {
            System.out.println(sql.replace("202210",""+yyyyMM));
        }
    }


    private static List<Integer> getYearMonthList(int beforeYear, int endYear) {
        List<Integer> rs=new ArrayList<>();
        int year = beforeYear / 100;
        int month = beforeYear % 100;
        while(year*100+month<=endYear) {
            int x = year * 100 + month;
//            System.out.println(x);
            rs.add(x);
            if(month>=12){
                year ++;
                month=1;
            }else{
                month++;
            }
        }
        return rs;
    }

    private static void genCacheTableAddColumn() {
        //TBL_SMS_LONG_MSGID_202008_61
        String sql = "ALTER TABLE TBL_SMS_MOBILES_CACHE ADD idMg bigint NULL;";
        for (int i = 202101; i <= 202112; i++) {
            for (int j = 0; j <= 99; j++) {
                String s = "drop table TBL_SMS_LONG_MSGID_" + i + "_" + fm(j) + ";";
                System.out.println(s);
            }
        }
    }

    private static String fm(int j) {
        return j < 10 ? "0" + j : "" + j;
    }
}

